#!/bin/sh
echo "---------------------开始清洗sys_user_info表的数据--------------------"
hive -v -e "set hive.exec.mode.local.auto=true; \
insert overwrite table dw.sys_user_info
select 
  user_id, 
  regexp_replace(user_name,'[^一-龥|a-z|A-Z]+','') as user_name, 
  job, 
  regexp_replace(dept_id, '[^0-9]','') as dept_id,
  password, 
  pwd_mod_status, 
  create_time, 
  mgr_id, 
  regexp_replace(mgr_name, '[^一-龥|a-z|A-Z]+','') mgr_name,
  org_id, 
  legal_rep, 
  if(substr(cer_number,  7,8)%2=0,'女','男') gender, 
  ceil(months_between(current_date,CONCAT(SUBSTRING(cer_number, 7, 4), '-', LPAD(SUBSTRING(cer_number, 11, 2), 2, '0'), '-',  
    LPAD(SUBSTRING(cer_number, 13, 2), 2, '0')))/12) as age, 
  CONCAT(SUBSTRING(cer_number, 7, 4), '-', LPAD(SUBSTRING(cer_number, 11, 2), 2, '0'), '-',  
    LPAD(SUBSTRING(cer_number, 13, 2), 2, '0')) birth_dt, 
  cer_type, 
  cer_number, 
  birth_addr, 
  off_short_num, 
  email, 
  old_user_id, 
  user_type, 
  operator_id, 
  last_mai_user_id, 
  last_mai_time, 
  last_mai_ins_id, 
  user_status, 
  regexp_replace(phone,'[^0-9]',''), 
  role_id, 
  re_dt, 
  remarks,
  current_timestamp as etl_time
  from ods.sys_user_info;"
echo $?
echo "-----------sys_user_info表已清洗完成--------"
echo "----------------验证数据-------------------------"
c1=`hive -e "set hive.exec.mode.local.auto=true;select count(*) from ods.sys_user_info;"`
echo "-----来源表数据量为:${c1}条"
c2=`hive -e "set hive.exec.mode.local.auto=true;select count(*) from dw.sys_user_info;"`
echo "-----落地表数据量为:${c2}条"
if [ "$c1" -le "$c2" ];then
echo "sys_user_info表清洗成功！"
else
echo "sys_user_info表清洗失败，请检查脚本"
fi

echo "---------------------开始清洗org_info表的数据--------------------"
hive -v -e "set hive.exec.mode.local.auto=true; \
insert overwrite table dw.org_info
select 
  orgid, 
  sortcode, 
  financial, 
  regexp_replace(orgname,'[^一-龥|a-z|A-Z]+','') as orgname, 
  regexp_replace(orgcompletename,'[^一-龥|a-z|A-Z]+','') as orgcompletename, 
  orgengname, 
  regexp_replace(orgrefname,'[^一-龥|a-z|A-Z]+','') as orgrefname, 
  porgid, 
  orglevel, 
  regexp_replace(orgphone,'[^0-9]+',''), 
  orgadress, 
  regexp_replace(orgprovince,'[^一-龥]+','') as orgprovince, 
  orgzipcode, 
  network, 
  istree, 
  orgauthority, 
  orgstatus, 
  orgsquence, 
  regexp_replace(orgtype,'[^一-龥]+','') as orgtype, 
  orgdatadate, 
  oldorgid, 
  oldorgtype, 
  orgmergeid, 
  notes, 
  establishdate, 
  orgbegindate, 
  orgenddate, 
  inputuserid, 
  inputusername, 
  updatedate,
  current_timestamp as etl_time
  from ods.org_info;"
echo $?
echo "-----------sys_user_info表已清洗完成--------"
echo "----------------验证数据-------------------------"
c1=`hive -e "set hive.exec.mode.local.auto=true;select count(*) from ods.org_info;"`
echo "-----来源表数据量为:${c1}条"
c2=`hive -e "set hive.exec.mode.local.auto=true;select count(*) from dw.org_info;"`
echo "-----落地表数据量为:${c2}条"
if [ "$c1" -le "$c2" ];then
echo "org_info表清洗成功！"
else
echo "org_info表清洗失败，请检查脚本"
fi
